[Python]CSVファイルをGoogleスプレッドシートにアップロードする

quiver

2018.06.26

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

他のチーム・メンバーとのインターフェースがGoogleスプレッドシートな皆様にとって、いかに楽にGoogleスプレッドシートを更新できるか、というのは頑張りどころです。

今回は、ローカルのCSVファイルをプログラマブルに新規Googleスプレッドシートに変換する方法を紹介します。

もちろん、スプレッドシートをブラウザから新規作成し、CSVファイルをアップロードすることもできますが、もっと楽をしたい怠惰なプログラマーにおすすめです。

2種類のアプローチ

以下の2種類のアプローチがあります。

Google Drive API を使い、CSV ファイルとして更新する方法
Google Sheets API を使い、スプレッドシートの追加、シートの更新をする方法

前者は、ローカルに CSV ファイルが有るケース、後者は、DB に SQL を投げたり、S3 のようなオブジェクトストレージからデータを読み込んで、CSV データを作成するケースで有用です。

順に試してみましょう。

使用環境

Linux/Mac
Python: 2.7

担当システムの制約から Python2.7 を利用しましたが、Python3 系でも軽微な修正で動作するはずです。

事前インストール

必要なライブラリを先にインストールしておきます

$ pip install -U oauth2client google-api-python-client

Google Drive API を使い、CSV ファイルとして更新

Google Drive API を使って、ローカルの CSV ファイルをもとに Google スプレッドシートを作成します。ローカルに CSV ファイルが有るケースなどで有用です。

プロジェクトの作成

Google Drive API を利用するために、Google APIキーコンソールに、アプリケーションを登録し、シークレットキーをダウンロードします。

手順は次のブログ記事の 「OAuthを使用する」を参照してください。

Google SpreadSheetにコマンドラインからCSVをアペンドする

注意点としては、ブログ記事は Google Sheets API を利用していますが、今回は Drive API を利用するため、アプリケーションの登録用URLが異なります(id パラメーター)。

ブラウザから下記のURLを開いてください。

https://console.developers.google.com/start/api?id=drive.googleapis.com

スクリプト

CSV ファイルをGoogleスプレッドシートにアップロードするスクリプトは以下です。

#!/usr/bin/env python
# coding:utf-8

from apiclient.discovery import build
from apiclient.http import MediaFileUpload
import oauth2client
import httplib2

CLIENT_SECRET_FILE = 'drive_client_secret.json'
CREDENTIAL_FILE = 'drive_credential.json'
APPLICATION_NAME = 'YOUR_APP_NAME'

SCOPES = 'https://www.googleapis.com/auth/drive' # Quickstarts と スコープを変える

store = oauth2client.file.Storage(CREDENTIAL_FILE)
creds = store.get()
if not creds or creds.invalid:
    flow = oauth2client.client.flow_from_clientsecrets(CLIENT_SECRET_FILE, SCOPES)
    flow.user_agent = APPLICATION_NAME
    creds = oauth2client.tools.run_flow(flow, store)
drive_service = build('drive', 'v3', http=creds.authorize(httplib2.Http())) # Setup the Drive v3 API

file_metadata = {
    'name': 'My Report',
    'mimeType': 'application/vnd.google-apps.spreadsheet'
}
media = MediaFileUpload('files/report.csv',
                        mimetype='text/csv',
                        resumable=True)

file = drive_service.files().create(body=file_metadata,
                                    media_body=media,
                                    fields='id').execute()
print 'File ID: %s' % file.get('id')

重要なところをかいつまんで紹介します。

スコープ

Google Drive APIs のQuickstarts ではスコープに "https://www.googleapis.com/auth/drive.metadata.readonly" を利用しています。

今回は、更新作業をおこなうため、OAuth の認可スコープは以下のいずれかを利用してください。

https://www.googleapis.com/auth/drive
https://www.googleapis.com/auth/drive.file
https://www.googleapis.com/auth/spreadsheets

また、 readonly で作成したクレデンシャルは、今回のスクリプトでは利用できませんので、再作成してください。

Google Drive のメタデータ

24-25 行目の file_metadata で Google Drive で作成するドキュメントのメタデータを設定しています。

name はファイル名です。
mimeType が spreadsheet になっていることに注意してください。

アップロードするファイルの定義

27-29 行目の media でアップロードするファイルを定義しています。

MediaFileUpload の第1引数は CSV ファイルのパスです。

ファイルの ID

アップロードが無事成功すると、34行目でファイルの ID が出力されます。このIDはスプレッドシートのIDです。

ブラウザでスプレッドシートで開いたときの URL は以下のスキーマをしています。

https://docs.google.com/spreadsheets/d/スプレッドシートID/edit#gid=シートID

スクリプト実行時に生成されたIDと Google スプレッドシートから確認できるIDが一致していることを確認してください。

認証を通す

最初の実行で、認証が必要です。

以下のコマンドを実行します。

$ python add_spreadsheet_with_drive_api.py --auth_host_name localhost --noauth_local_webserver

Go to the following link in your browser:

    https://accounts.google.com/o/oauth2/auth?scope=https...

Enter verification code: <ここにブラウザに表示されたコードを入力>

具体的な手順は次のブログ記事の 「認証を通す」を参照してください。

Google SpreadSheetにコマンドラインからCSVをアペンドする

CSVファイルをもとにスプレッドシートを作成する

以上で準備が整いましたので、 CSVデータを送ってみましょう。

$ cat files/report.csv
1,2,3
"a,b",c,d

$ python add_spreadsheet_with_drive_api.py
File ID: XXX

Googleドライブに移動すると、「My Report」という名前のGoogle スプレッドシートファイルが作成されているはずです。

Google Sheets API を使い、スプレッドシートの追加、シートの更新

次に Google Sheets API を使って Google スプレッドシートを作成します。

DB に SQL を投げたり、S3 のようなオブジェクトストレージからデータを読み込んで、オンメモリで CSV データを作成し、そこからスプレッドシートを作成するようなケースで有用です。

スクリプト

今回はスクリプトを単純にするために、スプレッドシート、及び、シートの作成は省き、CSVデータを更新したいスプレッドシート、及び、シートはすでに存在するものとします。

CSV データをGoogleスプレッドシートにアップロードするスクリプトは以下です。

スクリプトでは

スプレッドシートID − シートID

を利用しています。

ブラウザでスプレッドシートで開いたときの URL は以下のスキーマをしています。

https://docs.google.com/spreadsheets/d/スプレッドシートID/edit#gid=シートID

スクリプト実行時は、この URL から実際の ID に置換ください。

#!/usr/bin/env python
# coding:utf-8

from apiclient import discovery
import oauth2client
import httplib2
import argparse

SPREADSHEET_ID = 'XXX' # 実環境に合わせる
SHEET_ID = 'XXX'  # 実環境にあわせる
CLIENT_SECRET_FILE = 'client_secret.json'
CREDENTIAL_FILE = 'credential.json'
APPLICATION_NAME = 'XXX'

store = oauth2client.file.Storage(CREDENTIAL_FILE)
credentials = store.get()
if not credentials or credentials.invalid:
    SCOPES = 'https://www.googleapis.com/auth/spreadsheets'
    flow = oauth2client.client.flow_from_clientsecrets(CLIENT_SECRET_FILE, SCOPES)
    flow.user_agent = APPLICATION_NAME
    credentials = oauth2client.tools.run_flow(flow, store)

http = credentials.authorize(httplib2.Http())
discoveryUrl = ('https://sheets.googleapis.com/$discovery/rest?' 'version=v4')
service = discovery.build('sheets', 'v4', http=http, discoveryServiceUrl=discoveryUrl)

requests = {
    'pasteData': {
        # coordinate https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets#GridCoordinate
        'coordinate':{
          'sheetId': SHEET_ID,
          'rowIndex': 0,
          'columnIndex': 0
        },
        'data':'1,2,3\n"a,b",c,d\n',
        # PasteType https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request#PasteType
        'type':'PASTE_VALUES',
        'delimiter': ',',
    }
}

body = {
    'requests': requests
}

response = service.spreadsheets().batchUpdate(
  spreadsheetId=SPREADSHEET_ID,
  body=body).execute()

27-44行目が肝です。重要なところをかいつまんで紹介します。

データ更新はpasteData API を利用

28行目にあるように pasteData API を利用します。名前どおり、データを特定のシートの特定の位置にペーストします。

この位置は30行目からの coordinate で指定します。

詳細は次のドキュメントを参照ください。

https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets#GridCoordinate

この例では、指定したシートの1行目A列を起点にデータをペーストします。

ペーストスタイルは 37 行目の type 引数で指定します。今回は値だけをペーストするため、PASTE_VALUES を指定しています。

詳細は次のドキュメントを参照ください。

https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request#PasteType

更新データ

更新データは35行目の data 引数で指定します。

38 行目の delimiter から CSV データであることを表しています。

delimiter と html の引数を同時にすると、以下のエラーが発生します。エラーメッセージ通り、片方しか指定できないため、お気をつけください。

Union field kind . How to interpret the data, exactly one value must be set. kind can be only one of the following: "Invalid value at 'requests.paste_data' (oneof), oneoffield 'kind' is already set. Cannot set 'html'"

事前準備

スクリプトの実行に関して、最初の実行で、認証が必要です。

$ python update_sheet_with_csv.py  --auth_host_name localhost --noauth_local_webserver

Go to the following link in your browser:
 
    https://accounts.google.com/o/oauth2/auth?scope=https...
 
Enter verification code: <ここにブラウザに表示されたコードを入力>

具体的な手順は次のブログ記事の 「OAuthを使用する」と「認証を通す」を参照してください。

Google SpreadSheetにコマンドラインからCSVをアペンドする

CSVファイルをもとにスプレッドシートを作成する

以上で準備が整いましたので、 CSVデータを送ってみましょう。

$ python update_sheet_with_csv_data.py

Go to the following link in your browser:

    https://accounts.google.com/o/oauth2/auth?scope=https...

Enter verification code: <ここにブラウザに表示されたコードを入力>

Googleドライブに移動すると、指定したGoogle スプレッドシートファイルの指定したシートが更新されているはずです。